﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BBD_DAL.DBConnector;
using BBD_DAL.Entities;
using System.Data;
using MySql.Data.MySqlClient;

namespace BBD_DAL.DAO
{
	public class CityDao : IBaseDao<City>
	{
		private DBConnection dbConnector;
		private Dictionary<string, string> queries;

		public CityDao(string connectionString)
		{
			this.Initialize(connectionString);
		}

		public void Initialize(string connectionString)
		{
			this.dbConnector = new DBConnection(connectionString);
			queries = new Dictionary<string, string>();
			queries.Add("Get", "SELECT * FROM miejscowosci WHERE miejscowosc_id = @id;");
			queries.Add("GetByState", "SELECT * FROM miejscowosci WHERE wojewodztwo_id = @id;");
			queries.Add("GetByName", "SELECT * FROM miejscowosci WHERE miejscowosc_nazwa = @name;");
			
			queries.Add("Save", "UPDATE miejscowosci SET miejscowosc_nazwa = @name, wojewodztwo_id = @wid WHERE miejscowosc_id = @id;");
			queries.Add("Insert", "INSERT INTO miejscowosci (miejscowosc_nazwa, wojewodztwo_id) VALUES (@name, @wid);");
			queries.Add("Delete", "DELETE FROM miejscowosci WHERE id = @id;");
			queries.Add("DeleteAll", "DELETE FROM miejscowosci; ALTER TABLE miejscowosci AUTO_INCREMENT = 1;");
			queries.Add("List", "SELECT * FROM miejscowosci;");
		}

		public City Get(long id)
		{
			DataTable data;
			this.dbConnector.RunQuery(this.queries["Get"], new MySqlParameter[]{
				new MySqlParameter(){ParameterName = "@id", Value = id, MySqlDbType = MySqlDbType.Int64}
			}, out data);

			List<City> cities = this.Convert(data);

			return cities != null ? cities[0] : null;
		}

		public City Get(string name)
		{
			DataTable data;
			this.dbConnector.RunQuery(this.queries["GetByName"], new MySqlParameter[]{
				new MySqlParameter(){ParameterName = "@name", Value = name, MySqlDbType = MySqlDbType.String}
			}, out data);

			List<City> cities = this.Convert(data);

			return cities != null ? cities[0] : null;
		}

		public List<City> GetByState(long id)
		{
			DataTable data;
			this.dbConnector.RunQuery(this.queries["GetByState"], new MySqlParameter[]{
				new MySqlParameter(){ParameterName = "@id", Value = id, MySqlDbType = MySqlDbType.Int64}
			}, out data);

			List<City> cities = this.Convert(data);

			return cities != null ? cities : null;
		}

		public void Save(City entity)
		{
			this.dbConnector.RunQuery(this.queries["Save"], new MySqlParameter[]{
				new MySqlParameter(){ParameterName = "@id", Value = entity.Id, MySqlDbType = MySqlDbType.Int64},
				new MySqlParameter(){ParameterName = "@name", Value = entity.CityName, MySqlDbType = MySqlDbType.VarChar},
				new MySqlParameter(){ParameterName = "@wid", Value = entity.State.Id, MySqlDbType = MySqlDbType.Int64}
			});
		}

		public City Insert(City entity)
		{
			long id;
			this.dbConnector.RunQuery(this.queries["Insert"], new MySqlParameter[]{				
				new MySqlParameter(){ParameterName = "@name", Value = entity.CityName, MySqlDbType = MySqlDbType.VarChar},
				new MySqlParameter(){ParameterName = "@wid", Value = entity.State.Id, MySqlDbType = MySqlDbType.Int64}
			}, out id);

			entity.Id = id;

			return entity;
		}

		public void Delete(long id)
		{
			this.dbConnector.RunQuery(this.queries["Delete"], new MySqlParameter[]{
				new MySqlParameter(){ParameterName = "@id", Value = id, MySqlDbType = MySqlDbType.Int64}
			});
		}

		public void Delete(City entity)
		{
			this.Delete(entity.Id);
		}

		public void Delete()
		{
			this.dbConnector.RunQuery(this.queries["DeleteAll"], null);
		}

		public List<City> List()
		{
			DataTable data;
			this.dbConnector.RunQuery(this.queries["List"], null, out data);
			return this.Convert(data);
		}

		private List<City> Convert(DataTable data)
		{
			if (data == null || data.Rows == null || data.Rows.Count == 0)
				return null;

			List<City> cities = new List<City>();
			foreach (DataRow row in data.Rows)
			{
				City city = new City();
				city.Id = Int64.Parse(row["miejscowosc_id"].ToString());
				city.CityName = row["miejscowosc_nazwa"].ToString();
				city.State = new State() { Id = Int64.Parse(row["wojewodztwo_id"].ToString()) };
				cities.Add(city);
				
			}
			return cities;
		}
	}
}
